
IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_List_stp_auth_staff1')
          AND TYPE IN ('P','PC'))
DROP PROCEDURE USP_List_stp_auth_staff1
GO
/****** Object:  StoredProcedure [dbo].[USP_List_stp_auth_staff1]    Script Date: 11/19/2010 02:59:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		AKE
-- Create date: 17:36 18/9/2010
-- =============================================
CREATE PROCEDURE [dbo].[USP_List_stp_auth_staff1]
	-- Add the parameters for the stored procedure here
	@staff_code int,
	@current_staff_code int,
	@department_code int,
	@group_code int,
	@main_system_code int,
	@menu_system_code int,
	@auth_staff_status int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

    -- Modified by Boonyarit Yingvoragan
    -- Modified date 26/10/2553
    declare @master_department_level as integer
	declare @table as table(department_code int)
	declare @level as int
	
	if @department_code = 0
		begin
		
			if @staff_code = 0
				begin
					select @department_code = department_code from staff where staff_code = @current_staff_code
				end
			else
				begin
					select @department_code = department_code from staff where staff_code = @staff_code
				end
				
			select @master_department_level = mas.master_department_level 
			from master_department mas
			inner join stp_department dep on dep.master_department_code = mas.master_department_code 
			where @department_code = dep.department_code 

			insert @table 
			select dep.department_code 
			from stp_department dep
			inner join master_department mas on mas.master_department_code = dep.master_department_code 
			where mas.master_department_level >= @master_department_level
		end
	else
		begin
			select @master_department_level = mas.master_department_level 
			from master_department mas
			inner join stp_department dep on dep.master_department_code = mas.master_department_code 
			where @department_code = dep.department_code 

			insert @table 
			select department_code from stp_department where department_code = @department_code 
			
			set @level = @master_department_level
			while @level <= 10
			begin
				insert @table 
				select department_code 
				from stp_department 
				where department_parent in (select department_code from @table) 
				
				set @level = @level + 1
			end
		end

	-- =============================================
	-- Query data from authenticated group
	-- =============================================
	select	
			SeqNo = ROW_NUMBER() OVER (ORDER BY dept.department_code) ,
			auth_staff.staff_code staff_code,
			fullname = isnull(s.staff_firstname,'') + ' ' + isnull(s.staff_lastname,''),
			g.group_user_name,
			dept.department_name,
			sms.main_system_code,
			smas.main_system_name,
			sms.menu_system_name,
			ssm.sub_menu_name,
			mt.[type_name],
			dept.department_parent,
			mas.master_department_level,
			auth_staff.auth_staff_status
	from	stp_auth_department as auth
	join	stp_department as dept on auth.department_code = dept.department_code
	join	master_department as mas on mas.master_department_code = dept.master_department_code 
	join	stp_auth_group auth_group on auth_group.auth_dep_code = auth.auth_department_code 
	join	group_user g on g.group_user_code = auth_group.group_code 
	join	stp_auth_staff auth_staff on auth_staff.auth_dep_code = auth_group.auth_group_code 
	join	staff s on s.staff_code = auth_staff.staff_code
	join	stp_sub_menu as ssm on ssm.sub_menu_code = auth.sub_menu_code
	join	stp_menu_system as sms on sms.menu_system_code = ssm.menu_system_code
	join	stp_main_system as smas on smas.main_system_code = sms.main_system_code
	join	master_type as mt on mt.type_code = ssm.type_code
	where	auth.department_code in  (select department_code from @table where department_code is not null) and
			@group_code in (0, auth_group.group_code ) and
			@staff_code in (0, auth_staff.staff_code) and
			@auth_staff_status in (0, auth_staff.auth_staff_status ) and
			@main_system_code in (0,sms.main_system_code) and
			@menu_system_code in (0,ssm.menu_system_code) and
			@department_code in (0,dept.department_code)
	group by auth_staff.staff_code, s.staff_firstname, s.staff_lastname, 
				auth_group.group_code, g.group_user_name, dept.department_code , auth.department_code,
				dept.department_name ,auth_group.auth_group_status, dept.department_parent, 
				mas.master_department_level, sms.main_system_code, smas.main_system_name,auth_staff.auth_staff_status,
				ssm.menu_system_code, sms.menu_system_name, auth.sub_menu_code, ssm.sub_menu_name,mt.[type_name]

END
GO

